package br.skytef.stmanager.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import br.skytef.stmanager.entities.ControlPlanilhasEntity;
import br.skytef.stmanager.utils.Pool;

public class ControlPlanilhaDAO {

	private static final String INSERT = "INSERT INTO controlPlanilha(nomePlanilha, linhasImportadas, erros, dataImportacao) VALUES (?,?,?,?);";
	private static final String UPDATE = "UPDATE controlPlanilha SET nomePlanilha=?, linhasImportadas=?, erros=?, dataImportacao=? "
			+ "WHERE idcontrolPlanilha=?;";
	private static Connection conexao = null;

	public boolean inserir(ControlPlanilhasEntity controle){
		conexao = Pool.getConnection();
		PreparedStatement preparedStmt;
		try {
			preparedStmt = conexao.prepareStatement(INSERT);
			preparedStmt.setString(1, controle.getNomePlanilha());
			preparedStmt.setInt(2, controle.getLinhasImportadas());
			preparedStmt.setInt(3, controle.getErros());
			preparedStmt.setTimestamp(4, controle.getDataImportacao());
			preparedStmt.execute();
			conexao.close();			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}		
		return true;		
	}
	public boolean alterar(ControlPlanilhasEntity controle){
		conexao = Pool.getConnection();
		PreparedStatement preparedStmt;
		try {
			preparedStmt = conexao.prepareStatement(UPDATE);
			preparedStmt.setString(1, controle.getNomePlanilha());
			preparedStmt.setInt(2, controle.getLinhasImportadas());
			preparedStmt.setInt(3, controle.getErros());
			preparedStmt.setTimestamp(4, controle.getDataImportacao());
			preparedStmt.setInt(5, controle.getIdcontrolPlanilha());
			preparedStmt.execute();
			conexao.close();			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}		
		return true;		
	}

	public ControlPlanilhasEntity buscar(String planilha) throws SQLException {
		ControlPlanilhasEntity cpe = null;
		conexao = Pool.getConnection();
		Statement st = conexao.createStatement();
		ResultSet rs = st.executeQuery("SELECT * FROM controlPlanilha WHERE nomePlanilha LIKE '"+ planilha +"';");
		if (rs.next()) {      
			cpe = new ControlPlanilhasEntity();
			cpe.setIdcontrolPlanilha(rs.getInt("idcontrolPlanilha"));
			cpe.setNomePlanilha(rs.getString("nomePlanilha"));
			cpe.setLinhasImportadas(rs.getInt("linhasImportadas"));
			cpe.setErros(rs.getInt("erros"));
			cpe.setDataImportacao(rs.getTimestamp("dataImportacao"));
		}
		conexao.close();
		return cpe;
	}
}
